﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NRemedy;
using NRemedy.Linq;
using System.Configuration;
using Oracle.DataAccess.Client;

namespace Syncer
{
    public class IncidentImport
    {
        public Dictionary<string, Int32> _ComDic { get; set; }

        public Dictionary<string, Int32> _BsDic { get; set; }

        public void Execute(Param param)
        {
            Log.Info("start incident import.");
            //get application config
            string arserver = ConfigurationManager.AppSettings["ARServer"].ToString();
            string user = ConfigurationManager.AppSettings["User"].ToString();
            string pwd = ConfigurationManager.AppSettings["Pwd"].ToString();

            _ComDic = param.ComDic;
            _BsDic = param.BsDic;

            using (ARLoginContext context = new ARLoginContext(arserver, user, pwd))
            {
                ARSet<HPDHelpDesk> incidents = new ARSet<HPDHelpDesk>(context, new ARServerDefaultFactory());
                var query = from i in incidents
                            where i.Modifed_Date >= param.StartTime && i.Modifed_Date <= param.EndTime
                            select i;

                using (OracleDataAccess oda = new OracleDataAccess())
                {
                    foreach (var incident in query)
                    {
                        string sql = "select Request_ID from Incident where Request_ID=:requestId";
                        //incidentNumber must not be null
                        OracleParameter[] _param = new OracleParameter[] { 
                            new OracleParameter("requestId",incident.IncidentNumber)
                        };
                        object requestid = oda.ExecuteScalar(sql, _param);
                        //if not exsit,should be insert
                        if (requestid == null)
                        {
                            try
                            {
                                InsertIncident(incident, oda);
                                Log.Info("Insert " + incident.Request_ID + " OK.");
                            }
                            catch (Exception ex)
                            {
                                Log.Exception("Insert " + incident.Request_ID + " failed.", ex);
                            }
                        }
                        else
                        {
                            try
                            {
                                //update incident
                                UpdateIncident(incident, oda);
                                Log.Info("Update " + incident.Request_ID + " OK.");
                            }
                            catch (Exception ex)
                            {
                                Log.Exception("Update " + incident.Request_ID + " failed.", ex);
                            }
                        }
                    }
                }

            }
            Log.Info("incident import end.");
        }


        private void InsertIncident(HPDHelpDesk incident, OracleDataAccess oda)
        {
            string insert_sql = @"insert into Incident(
                                    Request_ID,
                                    SiteID,
                                    BusinessSystemId,
                                    Status,
                                    Category,
                                    Impact,
                                    Severity,
                                    Priority,
                                    Summury,
                                    Source,
                                    CloseCode,
                                    TicketCreateTime,
                                    TicketResolvedTime,
                                    ResolvedGroup,
                                    ResolvedTimeout,
                                    CreateTime,
FIRM,
FIRMPROSTATUS,
assigned_group,
assignee,
submitter_full_name
) 
                            values (
                                        :request_id,
                                        :siteid,
                                        :businesssystemid,
                                        :status,
                                        :category,
                                        :impact,
                                        :severity,
                                        :priority,
                                        :summary,
                                        :source,
                                        :closecode,
                                        :ticketcreatetime,
                                        :ticketresolvedtime,
                                        :resolvedgroup,
                                        :resolvedtimeout,
                                        :createtime,
:vendor,
:vendor_status,
:assigned_group,
:assignee,
:submitter
                                    )";
            OracleParameter[] param = new OracleParameter[] { 
                            new OracleParameter("request_id",incident.IncidentNumber),
                            //new OracleParameter("siteid",companyDic[incident.Company]),
                            //new OracleParameter("businesssystemid",string.IsNullOrEmpty(incident.BusinessSystem) ? null : (object)bsDic[incident.BusinessSystem + incident.Company]),
                            new OracleParameter("status",(int)incident.Status),
                            new OracleParameter("category",incident.Event1),
                            new OracleParameter("impact",(int)incident.Impact),
                            new OracleParameter("severity",(int)incident.Urgency),
                            new OracleParameter("priority",(int)incident.Priority),
                            new OracleParameter("summary",incident.Description),
                            new OracleParameter("source",(int)incident.ReportedSource),
                            new OracleParameter("closecode",(int)incident.Status_Reason),
                            new OracleParameter("ticketcreatetime",incident.Create_Date),
                            new OracleParameter("ticketresolvedtime",incident.LastResolvedDate),
                            new OracleParameter("resolvedgroup",HPDHelpDesk.ConvertGroup(incident.AssignedGroup)),
                            //new OracleParameter("resolvedtimeout",incident.LastResolvedDate == new DateTime(0) ? 0 : (incident.LastResolvedDate - incident.Create_Date).TotalSeconds - (double)incident.ResovledTimeLimit),
                            new OracleParameter("createtime",DateTime.Now),
                            new OracleParameter("vendor",incident.cChr_Vendor),
                            new OracleParameter("vendor_status",(int)incident.Vendor_Assignment_Status),
                            new OracleParameter("assigned_group",incident.AssignedGroup),
                            new OracleParameter("assignee",incident.Assignee),
                            new OracleParameter("submitter",incident.cChr_SubmitterFullName)
                        };
            var plist = param.ToList();

            //find businesssystemid
            if (string.IsNullOrEmpty(incident.BusinessSystem))
                plist.Add(new OracleParameter("businesssystemid", null));
            else if (!_BsDic.ContainsKey(incident.BusinessSystem + incident.Company))
                plist.Add(new OracleParameter("businesssystemid", null));
                //throw new Exception("Can not match " + incident.BusinessSystem + incident.Company);
            else
                plist.Add(new OracleParameter("businesssystemid", _BsDic[incident.BusinessSystem + incident.Company]));

            //find companyid
            if (incident.AssignedGroup == "中软海晟技术支持组" || incident.AssignedGroup == "海晟技术支持组")
            {
                incident.Company = "中软海晟技术支持组";
            }
            if (!_ComDic.ContainsKey(incident.Company))
                throw new Exception("Can not match " + incident.Company);
            plist.Add(new OracleParameter("siteid", _ComDic[incident.Company]));

            //calculate resolvedtimeout
            if (incident.LastResolvedDate == default(DateTime) || incident.ResovledTimeLimit == default(decimal))
                plist.Add(new OracleParameter("resolvedtimeout", null));
            else
                plist.Add(new OracleParameter("resolvedtimeout", (incident.LastResolvedDate - incident.Create_Date).TotalSeconds - (double)incident.ResovledTimeLimit));

            oda.ExecuteCommand(insert_sql, plist.ToArray());
        }

        private void UpdateIncident(HPDHelpDesk incident, OracleDataAccess oda)
        {
            string update_sql = @"update Incident set
                                    SiteID=	:siteid,
                                    BusinessSystemId=	:businesssystemid,
                                    Status=	:status,
                                    Category=	:category,
                                    Impact=	:impact,
                                    Severity=	:severity,
                                    Priority=	:priority,
                                    Summury=	:summary,
                                    Source=	:source,
                                    CloseCode=	:closecode,
                                    TicketCreateTime=	:ticketcreatetime,
                                    TicketResolvedTime=	:ticketresolvedtime,
                                    ResolvedGroup=	:resolvedgroup,
                                    ResolvedTimeout=	:resolvedtimeout,
FIRM= :vendor,
FIRMPROSTATUS= : vendor_status,
assigned_group=:assigned_group,
assignee=:assignee,
submitter_full_name=:submitter
                                    where Request_ID=:request_id
                                    ";
            OracleParameter[] param = new OracleParameter[] { 
                            //new OracleParameter("siteid",companyDic[incident.Company]),
                            //new OracleParameter("businesssystemid",string.IsNullOrEmpty(incident.BusinessSystem) ? null : (object)bsDic[incident.BusinessSystem + incident.Company]),
                            new OracleParameter("status",(int)incident.Status),
                            new OracleParameter("category",incident.Event1),
                            new OracleParameter("impact",(int)incident.Impact),
                            new OracleParameter("severity",(int)incident.Urgency),
                            new OracleParameter("priority",(int)incident.Priority),
                            new OracleParameter("summary",incident.Description),
                            new OracleParameter("source",(int)incident.ReportedSource),
                            new OracleParameter("closecode",(int)incident.Status_Reason),
                            new OracleParameter("ticketcreatetime",incident.Create_Date),
                            new OracleParameter("ticketresolvedtime",incident.Modifed_Date),
                            new OracleParameter("resolvedgroup",HPDHelpDesk.ConvertGroup(incident.AssignedGroup)),
                            //new OracleParameter("resolvedtimeout",incident.LastResolvedDate == new DateTime(0) ? 0 : (incident.LastResolvedDate - incident.Create_Date).TotalSeconds - (double)incident.ResovledTimeLimit),
                            new OracleParameter("request_id",incident.IncidentNumber),
                            new OracleParameter("vendor",incident.cChr_Vendor),
                            new OracleParameter("vendor_status",(int)incident.Vendor_Assignment_Status),
                             new OracleParameter("assigned_group",incident.AssignedGroup),
                            new OracleParameter("assignee",incident.Assignee),
                            new OracleParameter("submitter",incident.cChr_SubmitterFullName)
                        };

            var plist = param.ToList();

            //find businesssystemid
            if (string.IsNullOrEmpty(incident.BusinessSystem))
                plist.Add(new OracleParameter("businesssystemid", null));
            else if (!_BsDic.ContainsKey(incident.BusinessSystem + incident.Company))
                plist.Add(new OracleParameter("businesssystemid", null));
                //throw new Exception("Can not match " + incident.BusinessSystem + incident.Company);
            else
                plist.Add(new OracleParameter("businesssystemid", _BsDic[incident.BusinessSystem + incident.Company]));

            //find companyid
            if (incident.AssignedGroup == "中软海晟技术支持组" || incident.AssignedGroup == "海晟技术支持组")
            {
                incident.Company = "中软海晟技术支持组";
            }
            if (!_ComDic.ContainsKey(incident.Company))
                throw new Exception("Can not match " + incident.Company);
            plist.Add(new OracleParameter("siteid", _ComDic[incident.Company]));

            //calculate resolvedtimeout
            if (incident.LastResolvedDate == default(DateTime) || incident.ResovledTimeLimit == default(decimal))
                plist.Add(new OracleParameter("resolvedtimeout", null));
            else
                plist.Add(new OracleParameter("resolvedtimeout", (incident.LastResolvedDate - incident.Create_Date).TotalSeconds - (double)incident.ResovledTimeLimit));

            oda.ExecuteCommand(update_sql, plist.ToArray());
        }


    }
}
